'''
1、mysql 数据库操作
2、orm 框架运用，数据库关系映射
'''
import pymysql
from sqlalchemy.orm.session import Session


# 连接数据库
conn = pymysql.connect (host='192.168.60.241'
                       , user='newhope'
                       , passwd='newhope12345'
                       , port=3306
                       , db='test'
                       , charset='utf8')

cursor = conn.cursor()
# 使用 execute()  方法执行 SQL 查询
cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取单条数据.具体字段
cursor.execute('select * from order_info')
data1= cursor.fetchone().__getitem__(1)
print(data1)
# 2.执行插入语句
#cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
#cursor.execute('insert into user (id, name) values (%s, %s)', ['2', 'name2'])
# conn.commit()
# print(cursor.rowcount)
# cursor.execute('select * from user')
# user_date=cursor.fetchall()
# for row in user_date:
#     print('id:{},name:{}' .format(row[0],row[1]))

#3.执行查询
# cursor.execute('select * from user where id = %s', ('1',))
# select_data=cursor.fetchall()
# for row in select_data:
#     print('id:%s,name:%s', row[0],row[1])
# # 关闭数据库连接
# conn.close()


# orm
from sqlalchemy import Column, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker


# 创建对象的基类:
Base = declarative_base()

class User(Base):
    __tablename__='user'

    id=Column(String(20),primary_key=True)
    name=Column(String(20))

# # 初始化数据库连接:
engine = create_engine('mysql+pymysql://newhope:newhope12345@192.168.60.241:3306/test')
# # 创建DBSession类型:
DBSession = sessionmaker(bind=engine)

# session=DBSession()
# user= User(id='4',name='name3')
# session.add(user)
# session.commit()
# session.close()
# 查询语句

session = DBSession() ## type: Session
assert isinstance(session, Session) # 申明类型来解决，不能自动补全问题，python 动态语言问题，不能自动补全很多代码

user = session.query(User).order_by(User.id).all()
# 打印类型和对象的name属性:
print('type:', type(user))
print('name:', user[0].id)
# 关闭Session:
session.close()